#!/usr/bin/env python
# encoding: utf-8
'''
@author: 迟乐飞 
@contact: lefei_chi@foxmail.com
@software: pycharm
@file: city_adr.py
@time: 2019-11-20 08:44
@desc: 给王苏晓的途家城市/商圈的ADR，ADR计算规则，当前商圈卖出天数的价格以及房子的累计除以卖出的房子。
'''
import os
import sys
import re

# 当前文件的路径
pwd, filename = os.path.split(os.path.abspath(__file__))
# 当前文件的父路径
father_path = os.path.abspath(os.path.dirname(pwd) + os.path.sep + ".")
# 当前文件的前两级目录
grader_father = os.path.abspath(os.path.dirname(pwd) + os.path.sep + "..")
sys.path.append(pwd)
sys.path.append(father_path)
sys.path.append(grader_father)
# from report_system.utils.db_util import con_mysql_cityhome, con_mysql_report, con_mysql_report_test, con_mysql_tpp, con_mysql_youjia
from report_system.utils.db_util import con_mongodb_con
import logging
import pandas as pd

import datetime
import smtplib
from email.mime.text import MIMEText
from email.mime.multipart import MIMEMultipart
from email.mime.application import MIMEApplication


class CityADR(object):

    def __init__(self):
        pass

    def tujia_adr(self, city_id: int):
        # 纬度 城市/地区/日期
        conn = con_mongodb_con['tujia']['unit']
        contents = conn.find({'city_id': city_id})

        count = conn.find({'city_id': city_id}).count()
        # if count > 10000:
        logging.info('查询完成' + str(datetime.datetime.now()) + '编号{}'.format(city_id) + '数量{}'.format(str(count)))
        # print(count)
        tag_list, area_set, date_set = [], set(), set()
        n = 0
        for content in contents:
            n += 1
            try:
                tag_dict = {}
                tag_dict['city'], tag_dict['area'] = content['list']['cityName'], content['list']['unitInfor']
                for k, v in content['calendar_daily'].items():
                    tag_dict[k] = v
                    date_set.add(k)
                area_set.add(tag_dict['area'])
                tag_list.append(tag_dict)
            except Exception as e:
                # logging.error(datetime.datetime.now())
                continue
        _dates = sorted(date_set)
        output_list = []
        logging.warning('字段构造完成' + str(datetime.datetime.now()))
        area_count = len(area_set)
        n1 = 0
        for area in area_set:
            n1 += 1
            logging.info("地区:{} 已完成{}/{}       ".format(area, n1, area_count) + '    ' + str(
                datetime.datetime.now()))
            for _date in _dates:

                house_count, price, output_dict, house_all_count, error_nums = 0, 0, dict(), 0, 0
                for tag in tag_list:
                    if tag['area'] == area:
                        try:
                            if tag[_date]['status'] == 1:
                                error_nums = 0
                                house_all_count += 1
                                house_count += 1
                                price += tag[_date]['price']
                            else:
                                house_all_count += 1
                        except Exception as e:
                            continue
                output_dict['城市'] = tag['city']
                output_dict['区域'] = area
                try:
                    output_dict['ADR'] = round(price / house_count, 2)
                except:
                    output_dict['ADR'] = 0
                output_dict['日期'] = _date
                output_dict['卖出房源数量'] = house_count
                output_dict['存在房源数量'] = house_all_count
                output_list.append(output_dict)
        logging.info('准备转化DataFrame输出Excel' + str(datetime.datetime.now()))
        test = pd.DataFrame(output_list)
        test.to_csv('tujia_adr.csv', mode='a')
        logging.warning('完成' + str(datetime.datetime.now()))
        # print(test)

    def tujia_city_adr(self, city_id: int):
        # 纬度 城市/日期
        conn = con_mongodb_con['tujia']['unit']
        contents = conn.find({'city_id': city_id})

        count = conn.find({'city_id': city_id}).count()
        logging.info('查询完成' + str(datetime.datetime.now()) + '编号{}'.format(city_id) + '数量{}'.format(str(count)))
        tag_list, area_set, date_set = [], set(), set()
        n = 0
        for content in contents:
            n += 1
            try:
                tag_dict = {}
                tag_dict['city'], tag_dict['area'] = content['list']['cityName'], content['list']['unitInfor']
                for k, v in content['calendar_daily'].items():
                    tag_dict[k] = v
                    date_set.add(k)
                area_set.add(tag_dict['area'])
                tag_list.append(tag_dict)
            except Exception as e:
                continue
        _dates = sorted(date_set)
        output_list = []
        logging.warning('字段构造完成' + str(datetime.datetime.now()))
        for _date in _dates:
            house_count, price, output_dict, house_all_count, error_nums = 0, 0, dict(), 0, 0
            for tag in tag_list:
                try:
                    if tag[_date]['status'] == 1:
                        house_all_count += 1
                        house_count += 1
                        price += tag[_date]['price']
                    else:
                        house_all_count += 1
                except Exception as e:
                    continue

            output_dict['城市'] = tag['city']
            output_dict['区域'] = ''
            try:
                output_dict['ADR'] = round(price / house_count, 2)
            except:
                output_dict['ADR'] = 0
            output_dict['日期'] = _date
            output_dict['卖出房源数量'] = house_count
            output_dict['存在房源数量'] = house_all_count
            output_list.append(output_dict)
        logging.info('准备转化DataFrame输出Excel' + str(datetime.datetime.now()))
        test = pd.DataFrame(output_list)
        test.to_csv('tujia_city_adr.csv', mode='a')
        logging.warning('完成' + str(datetime.datetime.now()))
        # print(test)

    def tujia_city_month_adr(self, city_id: int):
        # 纬度 城市/日期
        conn = con_mongodb_con['tujia']['unit']
        contents = conn.find({'city_id': city_id})

        count = conn.find({'city_id': city_id}).count()
        logging.info('查询完成' + str(datetime.datetime.now()) + '编号{}'.format(city_id) + '数量{}'.format(str(count)))
        tag_list, area_set, date_set = [], set(), set()
        n = 0
        for content in contents:
            n += 1
            try:
                tag_dict = {}
                tag_dict['city'], tag_dict['area'] = content['list']['cityName'], content['list']['unitInfor']
                for k, v in content['calendar_daily'].items():
                    tag_dict[k] = v
                    date_set.add(k)
                area_set.add(tag_dict['area'])
                tag_list.append(tag_dict)
            except Exception as e:
                # logging.error(datetime.datetime.now())
                continue
        _dates = sorted(date_set)
        output_list = []
        logging.warning('字段构造完成' + str(datetime.datetime.now()))
        date_n = 0
        house_count, price, output_dict, house_all_count, error_nums = 0, 0, dict(), 0, 0
        for _date in _dates:
            for tag in tag_list:
                try:
                    if tag[_date]['status'] == 1:
                        house_all_count += 1
                        house_count += 1
                        price += tag[_date]['price']
                    else:
                        house_all_count += 1
                except Exception as e:
                    continue
            try:
                date_n += 1
                _date1, _date2 = re.sub('-\d{2}$', '', _dates[date_n - 1]), re.sub('-\d{2}$', '', _dates[date_n])
                if _date2 == _date1:
                    continue
            except Exception as e:
                logging.error(e)
            output_dict['城市'] = tag['city']
            output_dict['区域'] = ''
            try:
                output_dict['ADR'] = round(price / house_count, 2)
            except:
                output_dict['ADR'] = 0
            output_dict['日期'] = _date1
            output_dict['卖出房源数量'] = house_count
            output_dict['存在房源数量'] = house_all_count
            house_count, price, output_dict, house_all_count, error_nums = 0, 0, dict(), 0, 0
            output_list.append(output_dict)
        logging.info('准备转化DataFrame输出Excel' + str(datetime.datetime.now()))
        test = pd.DataFrame(output_list)
        test.to_csv('tujia_city_month_adr.csv', mode='a')
        logging.warning('完成' + str(datetime.datetime.now()))

    def tujia_city_area_month_adr(self, city_id: int):
        # 纬度 城市/地区/月
        conn = con_mongodb_con['tujia']['unit']
        contents = conn.find({'city_id': city_id})

        count = conn.find({'city_id': city_id}).count()
        # if count > 10000:
        logging.info('查询完成' + str(datetime.datetime.now()) + '编号{}'.format(city_id) + '数量{}'.format(str(count)))
        # print(count)
        tag_list, area_set, date_set = [], set(), set()
        n = 0
        for content in contents:
            n += 1
            try:
                tag_dict = {}
                tag_dict['city'], tag_dict['area'] = content['list']['cityName'], content['list']['unitInfor']
                for k, v in content['calendar_daily'].items():
                    tag_dict[k] = v
                    date_set.add(k)
                area_set.add(tag_dict['area'])
                tag_list.append(tag_dict)
            except Exception as e:
                # logging.error(datetime.datetime.now())
                continue
        _dates = sorted(date_set)
        output_list = []
        logging.warning('字段构造完成' + str(datetime.datetime.now()))
        area_count = len(area_set)
        n1 = 0
        for area in area_set:
            n1 += 1
            logging.info("地区:{} 已完成{}/{}       ".format(area, n1, area_count) + '    ' + str(
                datetime.datetime.now()))
            date_n = 0
            for _date in _dates:
                house_count, price, output_dict, house_all_count, error_nums = 0, 0, dict(), 0, 0
                for tag in tag_list:
                    if tag['area'] == area:
                        try:
                            if tag[_date]['status'] == 1:
                                house_all_count += 1
                                house_count += 1
                                price += tag[_date]['price']
                            else:
                                house_all_count += 1
                        except Exception as e:
                            continue
                try:
                    date_n += 1
                    _date1, _date2 = re.sub('-\d{2}$', '', _dates[date_n - 1]), re.sub('-\d{2}$', '', _dates[date_n])
                    if _date2 == _date1:
                        continue
                except Exception as e:
                    logging.error(e)

                output_dict['城市'] = tag['city']
                output_dict['区域'] = area
                try:
                    output_dict['ADR'] = round(price / house_count, 2)
                except:
                    output_dict['ADR'] = 0
                output_dict['日期'] = _date
                output_dict['卖出房源数量'] = house_count
                output_dict['存在房源数量'] = house_all_count
                output_list.append(output_dict)
        logging.info('准备转化DataFrame输出Excel' + str(datetime.datetime.now()))
        test = pd.DataFrame(output_list)
        test.to_csv('tujia_city_area_month_adr.csv', mode='a')
        logging.warning('完成' + str(datetime.datetime.now()))
        # print(test)

    def run_tujia(self):
        conn = con_mongodb_con['tujia']['city']
        contents = conn.find()
        # _context = [48, 10, 23, 45, 22, 17, 7, 8, 12, 49, 33, 36, 1, 55, 11, 72, 63, 65, 18, 251, 20, 9, 110, 44, 64,
        #             31, 126, 32, 111, 167, 30, 66, 51, 6]
        # _context = [10, 48, 23, 66]
        _context = ['北京', '南京', '天津', '重庆', '苏州', '青岛', '成都', '重庆', '成都', '长沙', '上海', '宁波', '南京', '西安', '武汉', '深圳',
                    '广州', '杭州', '昆明', '苏州', '贵阳', '武汉', '西安', '杭州', '淮安', '郑州', '天津', '桂林', '广州', '三亚', '厦门', '太原',
                    '上海', '三亚', '昆明', '长沙', '南宁', '济南', '凉山', '郑州', '威海', '厦门', '大连', '全国', '上海', '扬州', '承德', '珠海',
                    '北京', '镇江', '海口', '绍兴', '合肥', '南充', '青岛', '太原']
        for content in contents:
            if content['city']['name'] not in _context:
                continue
            logging.warning(content['city']['name'])
            # self.tujia_city_adr(city_id=content['id'])
            self.tujia_city_month_adr(city_id=content['id'])

    # def send_email(self):
    #     mail_receivers = ['chilefei@city-home.cn']
    #     # mail_receivers = ['chilefei@city-home.cn']
    #     files = ['platfrom_adr_data.csv']
    #     # 邮件标签
    #     mail_lable = "迟乐飞"
    #     # 使用的邮箱的smtp服务器地址，这里是163的smtp地址
    #     mail_host = "smtp.exmail.qq.com"
    #     # 用户名，登录名
    #     mail_user = "chilefei@city-home.cn"
    #     # 授权码，注意这里不是密码是授权码
    #     mail_pass = "6AdBXLj4fNBBQoqW"
    #     # 邮箱的后缀
    #     mail_postfix = "city-home.cn"
    #     me = mail_lable + "<" + mail_user + ">"
    #     msg = MIMEMultipart()
    #     msg.attach(MIMEText('请查收,爱彼迎的账单\n ps:sheet2表中有采集的详细记录的', _subtype='', _charset='utf-8'))
    #     msg['Subject'] = "爱彼迎账单"
    #     msg['From'] = me
    #     msg['To'] = ",".join(mail_receivers)  # 将收件人列表以‘,’分隔
    #     print(len(files))
    #     if len(files) != 0:
    #         for i in range(len(files)):
    #
    #             if os.path.isfile(files[i]):
    #                 part = MIMEApplication(open(files[i], 'rb').read())
    #                 part.add_header('Content-Disposition', 'attachment', filename=files[i])
    #                 msg.attach(part)
    #
    #     try:
    #         server = smtplib.SMTP()
    #         server.connect(mail_host)
    #         server.login(mail_user, mail_pass)  # 登录操作
    #         server.sendmail(me, mail_receivers, msg.as_string())
    #         server.close()
    #     except Exception as e:
    #         print(e)
    #         return False
    #     return True
    #
    #     pass


if __name__ == '__main__':
    CityADR().run_tujia()
    # logging.warning('查询完成' + str(datetime.datetime.now()))
